<?php
require_once dirname(__FILE__) . '/PHPExcel/PHPExcel.php';
$objPHPExcel = new PHPExcel();
$sheet = $objPHPExcel->getActiveSheet();

$array_data = array(
	'2'=> array('A'=>'KT1','B'=>'Michael','C'=>'2010-05-25','D'=>'8',),
	'3'=> array('A'=>'KT1','B'=>'Michael','C'=>'2010-05-26','D'=>'9',),
	'4'=> array('A'=>'KT1','B'=>'Michael','C'=>'2010-05-27','D'=>'7',),
	'5'=> array('A'=>'KT1','B'=>'Michael','C'=>'2010-05-28','D'=>'9',),
	'6'=> array('A'=>'KT1','B'=>'Michael','C'=>'2010-05-29','D'=>'7',),
	'7'=> array('A'=>'KT1','B'=>'Michael','C'=>'2010-05-30','D'=>'9',),
	'8'=> array('A'=>'KT2','B'=>'Dandy','C'=>'2010-05-25','D'=>'8',),
	'9'=> array('A'=>'KT2','B'=>'Dandy','C'=>'2010-05-26','D'=>'7',),
	'10'=> array('A'=>'KT2','B'=>'Dandy','C'=>'2010-05-27','D'=>'9',),
	'11'=> array('A'=>'KT2','B'=>'Dandy','C'=>'2010-05-28','D'=>'7',),
	'12'=> array('A'=>'KT2','B'=>'Dandy','C'=>'2010-05-29','D'=>'8',),
	'13'=> array('A'=>'KT2','B'=>'Dandy','C'=>'2010-05-30','D'=>'9',),
	'14'=> array('A'=>'KT2','B'=>'Dandy','C'=>'2010-05-31','D'=>'7',),
	'15'=> array('A'=>'KT2','B'=>'Dandy','C'=>'2010-06-01','D'=>'8',),
	'16'=> array('A'=>'KT3','B'=>'Stormy','C'=>'2010-05-25','D'=>'5',),
	'17'=> array('A'=>'KT3','B'=>'Stormy','C'=>'2010-05-26','D'=>'8',),
	'18'=> array('A'=>'KT3','B'=>'Stormy','C'=>'2010-05-27','D'=>'7',),
	'19'=> array('A'=>'KT3','B'=>'Stormy','C'=>'2010-05-28','D'=>'8',),
	'20'=> array('A'=>'KT3','B'=>'Stormy','C'=>'2010-05-29','D'=>'9',),
	'21'=> array('A'=>'KT3','B'=>'Stormy','C'=>'2010-05-30','D'=>'7',),
	'22'=> array('A'=>'KT4','B'=>'Cecil','C'=>'2010-05-25','D'=>'5',),
	'23'=> array('A'=>'KT4','B'=>'Cecil','C'=>'2010-05-26','D'=>'9',),
	'24'=> array('A'=>'KT4','B'=>'Cecil','C'=>'2010-05-27','D'=>'7',),
	'25'=> array('A'=>'KT4','B'=>'Cecil','C'=>'2010-05-28','D'=>'8',),
);

//this array should be generated by iterating through $array_data. However, for this turorial, it's already predefined
$array_distict_date = array('2010-05-25', '2010-05-26', '2010-05-27', '2010-05-28', '2010-05-29', '2010-05-30', '2010-05-31', '2010-06-01');
$array_data_user = array(
	'KT1|Michael' => array(
		'2010-05-25'=>8,'2010-05-26'=>9,'2010-05-27'=>7,'2010-05-28'=>9,
		'2010-05-29'=>7,'2010-05-30'=>9,
		),
	'KT2|Dandy' => array(
		'2010-05-25'=>8,'2010-05-26'=>7,'2010-05-27'=>9,'2010-05-28'=>7,
		'2010-05-29'=>8,'2010-05-30'=>9,'2010-05-31'=>7,'2010-06-01'=>8,
		),
	'KT3|Stormy' => array(
		'2010-05-25'=>5,'2010-05-26'=>8,'2010-05-27'=>7,'2010-05-28'=>8,
		'2010-05-29'=>9,'2010-05-30'=>7,
		),
	'KT4|Cecil' => array(
		'2010-05-25'=>5,'2010-05-26'=>9,'2010-05-27'=>7,'2010-05-28'=>8,
		),
);
//creating header
$sheet->setCellValue('A1', 'ID');
$sheet->setCellValue('B1', 'Name');
$sheet->setCellValue('C1', 'Date');
$starting_pos = ord('C');
$index_pos = 0;
foreach($array_distict_date as $date){
	$sheet->setCellValue(chr($starting_pos+$index_pos) . '2', $date);
	$index_pos++;
}

//doing merge cell
$sheet->mergeCells('A1:A2');
$sheet->mergeCells('B1:B2');
$sheet->mergeCells('C1:' . chr($starting_pos+($index_pos-1)) . '1');

//resize column dimension
for($i = 0; $i < $index_pos; $i++){
	$sheet->getColumnDimensionByColumn(2+$i, 2)->setWidth(10);
}

//setting cell alignment
$sheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
$sheet->getStyle('B1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
$sheet->getStyle('C1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);

//setting cell border and background fill
$default_border = array(
	'style' => PHPExcel_Style_Border::BORDER_THIN,
	'color' => array('rgb'=>'1006A3')
);
$style_header = array(
	'borders' => array(
		'bottom' => $default_border,
		'left' => $default_border,
		'top' => $default_border,
		'right' => $default_border,
	),
	'fill' => array(
		'type' => PHPExcel_Style_Fill::FILL_SOLID,
		'color' => array('rgb'=>'E1E0F7'),
	),
	'font' => array(
		'bold' => true,
	)
);

$style_body_content = array(
	'borders' => array(
		'bottom' => $default_border,
		'left' => $default_border,
		'top' => $default_border,
		'right' => $default_border,
	),
);



$sheet->getStyle('A1:A2')->applyFromArray( $style_header );

$sheet->getStyle('B1:B2')->applyFromArray( $style_header );
$sheet->getStyle('C1:' . chr($starting_pos+($index_pos-1)) . '1')->applyFromArray( $style_header );
for($i = $starting_pos; $i < ($starting_pos+$index_pos); $i++){
	$sheet->getStyle(chr($i) . '2')->applyFromArray( $style_header );
}

$rowcounter = 2;
$starting_pos = ord('C');
foreach($array_data_user as $k1=>$v1){
	$rowcounter++;
	list($uid, $uname) = explode("|", $k1);
	$sheet->setCellValue('A' . $rowcounter, $uid);
	$sheet->setCellValue('B' . $rowcounter, $uname);
	
	$sheet->getStyle('A' . $rowcounter)->applyFromArray( $style_body_content );
	$sheet->getStyle('B' . $rowcounter)->applyFromArray( $style_body_content );
	
	$index_pos = 0;
	foreach($array_distict_date as $date){
		$vdate = isset($v1[$date]) ? $v1[$date] : '-';
		$sheet->setCellValue(chr($starting_pos+$index_pos) . $rowcounter, $vdate);
		$sheet->getStyle(chr($starting_pos+$index_pos) . $rowcounter)->applyFromArray( $style_body_content );
		$index_pos++;
	}
}

//Adding total column
$ranged_total_coordinate = chr($starting_pos+$index_pos) . '1:' . chr($starting_pos+$index_pos) . '2';
$sheet->setCellValue(chr($starting_pos+$index_pos) . '1', 'Total');
$sheet->mergeCells($ranged_total_coordinate);
$sheet->getStyle(chr($starting_pos+$index_pos) . '1')->getAlignment()
		->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
		->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
$sheet->getStyle($ranged_total_coordinate)->applyFromArray( $style_header );
$rowcounter = 2;
foreach($array_data_user as $k1=>$v1){
	$rowcounter++;
	$sheet->setCellValue(chr($starting_pos+$index_pos) . $rowcounter, "=SUM(C{$rowcounter}:".chr(($starting_pos+($index_pos-1)))."{$rowcounter})");
	$sheet->getStyle(chr($starting_pos+$index_pos) . $rowcounter)->applyFromArray( $style_body_content );
}

//die($sheet->getCell('K3')->getValue() . " -> " . $sheet->getCell('K3')->getCalculatedValue());

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="rekap_tahunan.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');

